﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace NotificationServer.DataAccessLayer
{
    public class DbAccess
    {
        private SqlDataAdapter _sqlAdapter;        
        private string _dbConfigurationName = "NotificationServiceDB";
        private string _defaultConnectionString = @"Data Source=bolsredm201\bc_rechte;Initial Catalog=NotificationService;Persist Security Info=True;User ID=remote;Pwd=Test1234";
        private string _connectionString;

        /// <summary>
        /// Initialise Connection
        /// </summary>
        public DbAccess()
        {
            ConnectionStringSettings settings = ConfigurationManager.ConnectionStrings[_dbConfigurationName];

            if (settings == null)
            {
                settings.ConnectionString = _defaultConnectionString;

                // This code Snippet would create a new if no config found

                /*settings = new ConnectionStringSettings();
                settings.Name = _dbConfigurationName;
                settings.ConnectionString = _defaultConnectionString;
                Configuration config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
                config.Save();*/
            }
            _connectionString = settings.ConnectionString;            
        }

        public DataTable SelectQuery(string query, SqlParameter[] sqlParams)
        {
            SqlConnection con = null;
            SqlCommand cmd = new SqlCommand();
            _sqlAdapter = new SqlDataAdapter();
            DataTable da = new DataTable();
            DataSet ds = new DataSet();

            try
            {
                con = new SqlConnection(_connectionString);
                con.Open();
                cmd.Connection = con;
                cmd.CommandText = query;
                if (sqlParams != null)
                {
                    cmd.Parameters.AddRange(sqlParams);
                }
                
                cmd.ExecuteNonQuery();                
                _sqlAdapter.SelectCommand = cmd;
                _sqlAdapter.Fill(ds);
                da = ds.Tables[0];

            }
            catch(SqlException ex)
            {
                throw;
            }
            finally
            {
                if(con != null)
                    con.Close();
            }

            return da;
        }

        /// <method>
        /// Insert Query
        /// </method>
        public bool InsertQuery(string query, SqlParameter[] sqlParameter)
        {
            SqlConnection con = null;
            SqlCommand cmd = new SqlCommand();
            _sqlAdapter = new SqlDataAdapter();
            try
            {
                con = new SqlConnection(_connectionString);
                con.Open();
                cmd.Connection = con;
                cmd.CommandText = query;
                cmd.Parameters.AddRange(sqlParameter);
                _sqlAdapter.InsertCommand = cmd;
                cmd.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw;                
            }
            finally
            {
                if (con != null)
                    con.Close();
            }
            return true;
        }

        /// <method>
        /// Update Query
        /// </method>
        public int UpdateQuery(string query, SqlParameter[] sqlParameter)
        {
            SqlConnection con = null;
            SqlCommand cmd = new SqlCommand();
            _sqlAdapter = new SqlDataAdapter();
            int nAffectedRows;
            try
            {
                con = new SqlConnection(_connectionString);
                con.Open();
                cmd.Connection = con;
                cmd.CommandText = query;
                cmd.Parameters.AddRange(sqlParameter);
                _sqlAdapter.UpdateCommand = cmd;
                nAffectedRows = cmd.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                // TODO: Exception Handling not implemented yet
                throw;                
            }
            finally
            {
                if (con != null)
                    con.Close();
            }
            return nAffectedRows;
        }

        public int DeleteQuery(string query, SqlParameter[] sqlParameter)
        {
            SqlConnection con = null;
            SqlCommand cmd = new SqlCommand();
            _sqlAdapter = new SqlDataAdapter();
            int nAffectedRows;
            try
            {
                con = new SqlConnection(_connectionString);
                con.Open();
                cmd.Connection = con;
                cmd.CommandText = query;
                cmd.Parameters.AddRange(sqlParameter);
                _sqlAdapter.UpdateCommand = cmd;
                nAffectedRows = cmd.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                // TODO: Exception Handling not implemented yet
                throw;
            }
            finally
            {
                if (con != null)
                    con.Close();
            }
            return nAffectedRows;
        }
    }
}